package com.lukebang.util.export.excel;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import com.lukebang.constant.CurtainGoodSpecialType;
import com.lukebang.dao.GoodDAO;
import com.lukebang.dao.GoodSpecialDAO;
import com.lukebang.pojo.Good;
import com.lukebang.pojo.GoodSpecial;
import com.lukebang.pojo.MeasureIndent;
import com.lukebang.util.JsonUtil;

/**
 * 
 * ExcelUtil 下午5:23:15 Copyright HEXUNz Inc. All right reserved. Email :
 * alldios@outlook.com
 * 
 */
public class IndentExcelUtil {
	/** 工作页名称 */
	private static String sheetName = "sheet1";

	public static void main(String[] args) throws Exception {
	}

	/**
	 * 得到Excel表中的值
	 * 
	 * @param hssfCell
	 *            Excel中的每一个格子
	 * @return Excel中每一个格子中的值
	 */
	public static String getValue(HSSFCell hssfCell) {
		return hssfCell.getStringCellValue();
	}

	/**
	 * 
	 * @param xls
	 *            ExcelDto实体类的一个对象
	 * @throws Exception
	 *             在导入Excel的过程中抛出异常
	 */
	public static String xlsDto2Excel(List<ExcelDto> xls, HttpServletResponse response) throws Exception {
		// 创建Excel文档
		HSSFWorkbook hwb = new HSSFWorkbook();
		ExcelDto xlsDto = null;
		// sheet 对应一个工作页
		HSSFSheet sheet = hwb.createSheet(sheetName);
		// 初始化 title
		HSSFCellStyle cellStyle = hwb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setBorderLeft(BorderStyle.valueOf((short) 1));
		cellStyle.setBorderRight(BorderStyle.valueOf((short) 1));
		cellStyle.setBorderBottom(BorderStyle.valueOf((short) 1));
		cellStyle.setBorderTop(BorderStyle.valueOf((short) 1));

		HSSFCellStyle titleType = hwb.createCellStyle();
		HSSFFont f = hwb.createFont();
		f.setBold(true);
		titleType.setFont(f);
		titleType.setAlignment(HorizontalAlignment.CENTER);
		titleType.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
		titleType.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		titleType.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleType.setFillBackgroundColor(HSSFColor.YELLOW.index);
		titleType.setBorderLeft(BorderStyle.valueOf((short) 1));
		titleType.setBorderRight(BorderStyle.valueOf((short) 1));
		titleType.setBorderBottom(BorderStyle.valueOf((short) 1));
		titleType.setBorderTop(BorderStyle.valueOf((short) 1));

		// 设置column的宽度
		for (int j = 0; j < 20; j++) {
			if (j == 15) {
				sheet.setColumnWidth(j, 256 * 60 + 184);
			} else {
				sheet.setColumnWidth(j, 20 * 256);
			}
		}

		Map<Integer, String> mergeMap = new HashMap<Integer, String>();
		StringBuilder mergeStr = new StringBuilder("");
		for (int i = 0; i < xls.size(); i++) {
			// 创建一行
			HSSFRow row = sheet.createRow(i);
			// 得到要插入的每一条记录
			xlsDto = xls.get(i);
			// 遍历数据并存入
			for (int colu = 0; colu <= xls.size(); colu++) {
				// 在一行内循环
				for (int cellNum = 0; cellNum < xlsDto.getData().length; cellNum++) {
					HSSFCell xh = row.createCell(cellNum);
					// 0 为真正的值 1 为右侧偏移量 2为下方偏移量 3为style
					String[] split = xlsDto.getData()[cellNum].toString().split("\\\\");
					if ("1".equals(split[1])) {
						row.setHeight((short) 600);
						xh.setCellStyle(titleType);
					} else {
						row.setHeight((short) 500);
						xh.setCellStyle(cellStyle);
					}
					xh.setCellValue(split[0]);
					if (split.length == 6) {
						if (Integer.valueOf(split[2]) != 0) {
							if (mergeMap.get(Integer.valueOf(split[2])) == null) {
								mergeStr.append(i + "-" + (i - 1 + Integer.valueOf(split[3])) + "-" + split[4]);
								mergeMap.put(Integer.valueOf(split[2]), mergeStr.toString());
								mergeStr.delete(0, mergeStr.length());
							}
						}
					}
				}
			}
		}
		System.out.println("mergeMap   :    " + JsonUtil.toJson(mergeMap));
		Set<Entry<Integer, String>> entrySet = mergeMap.entrySet();
		for (Entry<Integer, String> entry : entrySet) {
			String value = entry.getValue();
			String[] split = value.split("-");
			if (!split[0].equals(split[1])) {
				if ("0".equals(split[2])) {
					CellRangeAddress cra = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]), 0,
							0);
					CellRangeAddress cra2 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							1, 1);
					CellRangeAddress cra3 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							2, 2);
					CellRangeAddress cra4 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							3, 3);
					CellRangeAddress cra5 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							4, 4);
					// 在sheet里增加合并单元格
					sheet.addMergedRegion(cra);
					sheet.addMergedRegion(cra2);
					sheet.addMergedRegion(cra3);
					sheet.addMergedRegion(cra4);
					sheet.addMergedRegion(cra5);
				} else {
					CellRangeAddress cra6 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							0, 0);
					CellRangeAddress cra7 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							1, 1);
					CellRangeAddress cra8 = new CellRangeAddress(Integer.valueOf(split[0]), Integer.valueOf(split[1]),
							2, 4);
					sheet.addMergedRegion(cra6);
					sheet.addMergedRegion(cra7);
					sheet.addMergedRegion(cra8);
				}
			}
		}
		String fileName = DateUtils.getDate("yyyyMMdd_hhmmss") + ".xls";
		response.setContentType("application/x-download");
		response.setHeader("Content-Disposition",
				"attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
		ServletOutputStream outputStream = response.getOutputStream();
		BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);
		hwb.write(bufferedOutputStream);
		bufferedOutputStream.flush();
		bufferedOutputStream.close();
		outputStream.flush();
		outputStream.close();
		hwb.close();
		return fileName;
	}

	/**
	 * 将List对象转化为 ExcelDto
	 * 
	 * @return List<ExcelDto>对象
	 * @throws IOException
	 *             输入/输出(i/o)异常
	 */
	public static List<ExcelDto> measureIndent2ExcelDtos(List<MeasureIndent> measureIndents, GoodDAO goodDAO,
			GoodSpecialDAO goodSpecialDAO) throws IOException {
		List<ExcelDto> list = new ArrayList<ExcelDto>();
		for (MeasureIndent measureIndent : measureIndents) {
			// 生成标题====================================
			list.add(getIndentTitle());
			// 获取传来的对象数据
			String o = measureIndent.toString();
			// 获取 对象属性数据对
			String[] entrys = o.split("\\|");
			// 创建对应的ExcelRow 数据对象
			ExcelDto excelRowDto = new ExcelDto((short) entrys.length);
			// 获取当前的 行 Cell 的所有列 Row 数据
			for (int cellNum = 0; cellNum < entrys.length; cellNum++) {
				excelRowDto.getData()[cellNum] = entrys[cellNum];
			}
			list.add(excelRowDto);
			List<Good> goods = goodDAO.selectAllIndentGoodByIndent(measureIndent.getIndentId());
			System.out.println("goods:" + JsonUtil.toJson(goods));
			for (Good good : goods) {
				List<GoodSpecial> byGood = goodSpecialDAO.selectGoodSpecialsByGood(good.getGoodsId());
				list.add(getGoodTitle());
				for (GoodSpecial goodSpecial : byGood) {
					System.out.println("goodstyle:    " + good.getStyle());
					if (good.getShadeHead() != null) {
						if (good.getShadeHead() == 1) {
							good.setGoodSpecialSize(byGood.size() + 1);
						} else {
							good.setGoodSpecialSize(byGood.size());
						}
					} else {
						good.setGoodSpecialSize(byGood.size());
					}
					goodSpecial.setGood(good);
					// 生成标题====================================
					// 获取传来的对象数据
					o = goodSpecial.toString();
					// 获取 对象属性数据对
					entrys = o.split("\\|");
					// 创建对应的ExcelRow 数据对象
					excelRowDto = new ExcelDto((short) entrys.length);
					// 获取当前的 行 Cell 的所有列 Row 数据
					for (int cellNum = 0; cellNum < entrys.length; cellNum++) {
						excelRowDto.getData()[cellNum] = entrys[cellNum];
					}
					System.out.println("goodspecialtype:       " + goodSpecial.getType());
					if (goodSpecial.getType() == 2) {
						list.addAll(getHeadSizeTitle(goodSpecial));
						break;
					}
					list.add(excelRowDto);
				}
			}
		}
		return list;
	}

	public static ExcelDto getIndentTitle() {
		ExcelDto excelRowDto = new ExcelDto((short) 8);
		excelRowDto.getData()[0] = "订单号\\1";
		excelRowDto.getData()[1] = "订单类型\\1";
		excelRowDto.getData()[2] = "下单人\\1";
		excelRowDto.getData()[3] = "下单手机号\\1";
		excelRowDto.getData()[4] = "下单时间\\1";
		excelRowDto.getData()[5] = "预约时间\\1";
		excelRowDto.getData()[6] = "客户名称\\1";
		excelRowDto.getData()[7] = "客户地址\\1";
		return excelRowDto;
	}

	public static ExcelDto getGoodTitle() {
		ExcelDto excelRowDto = new ExcelDto((short) 16);
		excelRowDto.getData()[0] = "安装房间\\1";
		excelRowDto.getData()[1] = "窗帘类型\\1";
		excelRowDto.getData()[2] = "结构\\1";
		excelRowDto.getData()[3] = "款式\\1";
		excelRowDto.getData()[4] = "里布\\1";
		excelRowDto.getData()[5] = "尺寸号\\1";
		excelRowDto.getData()[6] = "宽\\1";
		excelRowDto.getData()[7] = "高\\1";
		excelRowDto.getData()[8] = "杆长\\1";
		excelRowDto.getData()[9] = "窗户打开方式\\1";
		excelRowDto.getData()[10] = "配件\\1";
		excelRowDto.getData()[11] = "装饰头\\1";
		excelRowDto.getData()[12] = "安装方式\\1";
		excelRowDto.getData()[13] = "安装配件\\1";
		excelRowDto.getData()[14] = "安装制作方法\\1";
		excelRowDto.getData()[15] = "测量位置拍照\\1";
		return excelRowDto;
	}

	public static List<ExcelDto> getHeadSizeTitle(GoodSpecial goodSpecial) {
		List<ExcelDto> excelDtos = new ArrayList<ExcelDto>();
		ExcelDto excelRowDto = new ExcelDto((short) 13);
		excelRowDto.getData()[0] = "无\\0";
		excelRowDto.getData()[1] = "无\\0";
		excelRowDto.getData()[2] = "无\\0";
		excelRowDto.getData()[3] = "无\\0";
		excelRowDto.getData()[4] = "无\\0";
		excelRowDto.getData()[5] = "帘头信息\\1";
		excelRowDto.getData()[6] = "安装情况\\1";
		excelRowDto.getData()[7] = "不满墙情况\\1";
		excelRowDto.getData()[8] = "帘头宽度\\1";
		excelRowDto.getData()[9] = "帘头配件\\1";
		excelRowDto.getData()[10] = "帘头轨宽度\\1";
		excelRowDto.getData()[11] = "配件\\1";
		excelRowDto.getData()[12] = "备注\\1";

		ExcelDto excelRowDto1 = new ExcelDto((short) 13);
		excelRowDto1.getData()[0] = "无\\0";
		excelRowDto1.getData()[1] = "无\\0";
		excelRowDto1.getData()[2] = "无\\0";
		excelRowDto1.getData()[3] = "无\\0";
		excelRowDto1.getData()[4] = "无\\0";
		excelRowDto1.getData()[5] = CurtainGoodSpecialType.getName(goodSpecial.getType() + 1) + "\\0";
		excelRowDto1.getData()[6] = "安装情况\\0";
		excelRowDto1.getData()[7] = "不满墙情况\\0";
		excelRowDto1.getData()[8] = "帘头宽度\\0";
		excelRowDto1.getData()[9] = "帘头配件\\0";
		excelRowDto1.getData()[10] = "帘头轨宽度\\0";
		excelRowDto1.getData()[11] = "配件\\0";
		excelRowDto1.getData()[12] = "备注\\0";
		excelDtos.add(excelRowDto);
		excelDtos.add(excelRowDto1);
		return excelDtos;
	}

	public String getSheetName() {
		return sheetName;
	}

	/** 设置sheet名 */
	public static void setSheetName(String sheet) {
		sheetName = sheet;
	}
}
